﻿using System.Data;
using System.Text;
using YookingCrypto.Class;
using YookingFileIO.Class;
using YookingSvrDBCall.Class.DBLogic;
using YookingSvrDBCall.Class.DBPub;

namespace YookingBizWQW.Class.Public
{
    public class clsComonFun
    {
        //注释：王庆文 2020年03月10日===================================================
        //查询照片图像库，获取指定库表的照片图像信息
        public string GetPhotoFromDB(string strB64RequestData)
        {
            string strRtData = "no data";

            //先进行解密，然后在进行中文解码，最后得到明文的请求数据
            string strRequestData = clsHandleData.DeUrlRequest(strB64RequestData);

            #region 将strRequestData包含的各种参数一一解析出来

            string strDBTableName = "WQWTEST"; //主库表名称
            string strSys_RecordID = "000000"; //主库记录ID
            string strSQLCondition = " 1=1 ";  //附加图像查询条件

            //这些参数用★分隔开，客户端与服务器端要一致
            string[] strArray = strRequestData.Split('★');
            if (strArray.Length >= 3)
            {
                strDBTableName = strArray[0];
                strSys_RecordID = strArray[1];
                strSQLCondition = strArray[2];
            }

            #endregion

            #region 生成查询记录的SQL语句

            clsDBSpace mySpace = new clsDBSpace(); //系统表空间名
            string strSysSapace = mySpace.GetDBSpaceName("Sys");

            StringBuilder sbQuerySQL = new StringBuilder();
            sbQuerySQL.AppendLine(" SELECT T.IMAGETYPE,T.IMAGEENTITY FROM MDA_PUBLICIMAGE T ");
            sbQuerySQL.AppendLine("   LEFT JOIN " + strSysSapace + ".DB_TABLEDEFINE A ");
            sbQuerySQL.AppendLine("     ON (T.MAINTABLEDEFINEID=A.TABLEID ");
            sbQuerySQL.AppendLine("         OR UPPER(T.MAINTABLEDEFINEID)='" + strDBTableName.ToUpper() + "') ");
            sbQuerySQL.AppendLine(" WHERE " + clsSQLSyntax.IsNullSQL("Data", "T.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND UPPER(A.TABLENAME)= '" + strDBTableName.ToUpper() + "' ");
            sbQuerySQL.AppendLine("   AND T.MAINTABLERECORDID = '" + strSys_RecordID + "' ");
            sbQuerySQL.AppendLine("   AND " + strSQLCondition);

            #endregion

            //clsRWFile myLog = new clsRWFile(); //将待执行的SQL语句记录下来
            //myLog.WriteInfoToTxtFile("SysLog", "wqw.txt", sbQuerySQL.ToString());

            clsDbSQLLogic myDB = new clsDbSQLLogic(); //实例化数据库操作类
            strRtData = myDB.YookingExecuteJson("Data", sbQuerySQL.ToString());

            return strRtData; //数据库查询结果
        }

        //注释：王庆文 2020年03月15日===================================================
        //查询照片图像库，获取指定库表的照片图像信息
        public string GetDBTableImageFromDB(string strB64RequestData)
        {
            string strRtData = "no data";

            //先进行解密，然后在进行中文解码，最后得到明文的请求数据
            string strRequestData = clsHandleData.DeUrlRequest(strB64RequestData);

            #region 将strRequestData包含的各种参数一一解析出来

            string strDBTableName = "WQWTEST";  //主库表名称
            string strReturnCols = "T.AA,T.BB"; //需要得到的字段集合
            string strSepImageCol = "T.AA";     //与SpecialRelateNumber相关联的字段名称
            string strSQLCondition = " 1=1 ";   //附加图像查询条件

            //这些参数用★分隔开，客户端与服务器端要一致
            string[] strArray = strRequestData.Split('★');
            if (strArray.Length >= 4)
            {
                strDBTableName = strArray[0];
                strReturnCols = strArray[1];
                strSepImageCol = strArray[2];
                strSQLCondition = strArray[3];
            }

            #endregion

            #region 生成查询记录的SQL语句

            //将表名称、字段名称全部转成大写字母
            strDBTableName = strDBTableName.ToUpper();
            strReturnCols = strReturnCols.ToUpper();
            strSepImageCol = strSepImageCol.ToUpper();

            clsDBSpace mySpace = new clsDBSpace(); //系统表空间名
            string strSysSapace = mySpace.GetDBSpaceName("Sys");

            StringBuilder sbQuerySQL = new StringBuilder();
            sbQuerySQL.AppendLine(" SELECT ");
            sbQuerySQL.AppendLine("   " + strReturnCols + ", ");
            sbQuerySQL.AppendLine("   B.IMAGEENTITY ");
            sbQuerySQL.AppendLine(" FROM " + strDBTableName + " T ");
            sbQuerySQL.AppendLine("   LEFT JOIN " + strSysSapace + ".DB_TABLEDEFINE A ON UPPER(A.TABLENAME)='" + strDBTableName + "' ");
            sbQuerySQL.AppendLine("   LEFT JOIN MDA_PUBLICIMAGE B ON (UPPER(B.MAINTABLEDEFINEID)='" + strDBTableName + "' OR B.MAINTABLEDEFINEID=A.TABLEID) AND B.MAINTABLERECORDID=T.SYS_RECORDID AND B.SPECIALRELATENUMBER=" + strSepImageCol);
            sbQuerySQL.AppendLine(" WHERE " + clsSQLSyntax.IsNullSQL("Data", "T.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND " + clsSQLSyntax.IsNullSQL("Data", "A.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND " + clsSQLSyntax.IsNullSQL("Data", "B.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND " + strSQLCondition);

            #endregion

            //clsRWFile myLog = new clsRWFile(); //将待执行的SQL语句记录下来
            //myLog.WriteInfoToTxtFile("SysLog", "wqw.txt", sbQuerySQL.ToString());

            clsDbSQLLogic myDB = new clsDbSQLLogic(); //实例化数据库操作类
            strRtData = myDB.YookingExecuteJson("Data", sbQuerySQL.ToString());

            return strRtData; //数据库查询结果
        }

        //注释：王庆文 2020年03月14日===================================================
        //将明文字符串加密之后，返回给客户端
        public string GetEncryptString(string strB64RequestData)
        {
            string strRtData = "[{\"result\":\"yooking null\"}]";

            //先进行解密，然后在进行中文解码，最后得到明文的请求数据
            string strRequestData = clsHandleData.DeUrlRequest(strB64RequestData);

            #region 将strRequestData包含的各种参数一一解析出来

            string strDeText = "123456"; //明文字符串

            //这些参数用★分隔开，客户端与服务器端要一致
            string[] strArray = strRequestData.Split('★');
            if (strArray.Length >= 1)
            {
                strDeText = strArray[0];
            }

            #endregion

            clsSecret myDes = new clsSecret();
            var strEnText = myDes.EncryptString(strDeText);

            strRtData = "[{\"result\":\"" + strEnText + "\"}]";

            return strRtData; //得到密文数据
        }

        //注释：王庆文 2020年03月20日======================================================
        //自定义库表名称、自定义查询条件，到数据库中查找符合条件的数据记录
        public string GetDBTableDataForCondition(string strB64RequestData)
        {
            string strRtData = "no data";

            //先进行解密，然后在进行中文解码，最后得到明文的请求数据
            string strRequestData = clsHandleData.DeUrlRequest(strB64RequestData);

            #region 将strRequestData包含的各种参数一一解析出来

            string strDBGroup = "Data";  //数据库连接串分组名
            string strDBTableName = "WQWTEST";  //主库表名称
            string strReturnCols = "T.AA,T.BB"; //需要得到的字段集合
            string strSQLCondition = " 1=1 ";   //附加查询条件

            //这些参数用★分隔开，客户端与服务器端要一致
            string[] strArray = strRequestData.Split('★');
            if (strArray.Length >= 4)
            {
                strDBGroup = strArray[0];
                strDBTableName = strArray[1];
                strReturnCols = strArray[2];
                strSQLCondition = strArray[3];
            }

            #endregion

            #region 生成查询记录的SQL语句

            //将表名称、字段名称全部转成大写字母
            strDBTableName = strDBTableName.ToUpper();
            strReturnCols = strReturnCols.ToUpper();

            StringBuilder sbQuerySQL = new StringBuilder();
            sbQuerySQL.AppendLine(" SELECT " + strReturnCols + " FROM " + strDBTableName + " T ");
            sbQuerySQL.AppendLine("   WHERE " + strSQLCondition);

            #endregion

            //clsRWFile myLog = new clsRWFile(); //将待执行的SQL语句记录下来
            //myLog.WriteInfoToTxtFile("SysLog", "wqw.txt", sbQuerySQL.ToString());

            clsDbSQLLogic myDB = new clsDbSQLLogic(); //实例化数据库操作类
            strRtData = myDB.YookingExecuteJson(strDBGroup, sbQuerySQL.ToString());

            return strRtData; //数据库查询结果
        }

        //注释：王庆文 2020年03月20日============================================================
        //按照三级联动的格式获取行政区划字典数据，适合APP端先选省份、再选城市、再选区县的操作
        public string GetXZQHDictSelecter_ThreeLevel(string strB64RequestData)
        {
            string strRtData = "no data";

            //先进行解密，然后在进行中文解码，最后得到明文的请求数据
            string strRequestData = clsHandleData.DeUrlRequest(strB64RequestData);

            #region 将strRequestData包含的各种参数一一解析出来

            string strDictDB = "WqwTest";  //字典表名称
            string strSQLExt = " 1=1 ";    //SQL语句的查询条件

            //这些参数用★分隔开，客户端与服务器端要一致
            string[] strArray = strRequestData.Split('★');
            if (strArray.Length >= 2)
            {
                strDictDB = strArray[0];
                strSQLExt = strArray[1];
            }

            #endregion

            #region 查询省份、城市、区县信息，并合成特定的格式

            //省
            StringBuilder sbProvinceSQL = new StringBuilder();
            sbProvinceSQL.AppendLine(" select dictcode Province,dictmemo  from " + strDictDB + " where DictCode like '%0000' order by Province");

            clsDbSQLLogic myDB = new clsDbSQLLogic(); //数据库的查询结果
            DataSet dsProvince = myDB.YookingExecuteDataSet("Dict", sbProvinceSQL.ToString());

            //市
            StringBuilder sbCitySQL = new StringBuilder();
            sbCitySQL.AppendLine(" select CONCAT_WS('',substr(dictcode,1,2),'0000') as Province,dictcode as City,dictmemo from  " + strDictDB + "  where DictCode like '%00' order by City");

            DataSet dsCity = myDB.YookingExecuteDataSet("Dict", sbCitySQL.ToString());
            //县
            StringBuilder sbTownSQL = new StringBuilder();
            sbTownSQL.AppendLine(" select CONCAT_WS('',substr(dictcode,1,4),'00') as City,dictcode as Town,dictmemo from  " + strDictDB + "  order by Town ");

            DataSet dsTown = myDB.YookingExecuteDataSet("Dict", sbTownSQL.ToString());

            if (dsProvince != null && dsProvince.Tables.Count > 0 && dsProvince.Tables[0].Rows.Count > 0)
            {
                strRtData = "[";
                foreach (DataRow dr in dsProvince.Tables[0].Rows)
                {
                    strRtData += "{ \"id\": \"" + dr["PROVINCE"] + "\",\"name\": \"" + dr["DICTMEMO"] + "\",\"child\":[";
                    DataRow[] drr = dsCity.Tables[0].Select("PROVINCE = '" + dr["PROVINCE"] + "'");//市级
                    foreach (DataRow drs in drr)
                    {
                        string CityDictMemo;
                        if (drs["DICTMEMO"].ToString() == dr["DICTMEMO"].ToString())
                        {
                            CityDictMemo = drs["DICTMEMO"].ToString();
                        }
                        else
                        {
                            CityDictMemo = drs["DICTMEMO"].ToString().Replace(dr["DICTMEMO"].ToString(), "");
                        }

                        strRtData += "{ \"id\": \"" + drs["CITY"] + "\", \"name\": \"" + CityDictMemo + "\",\"child\":[";
                        DataRow[] drrr = dsTown.Tables[0].Select("CITY = '" + drs["CITY"] + "'");//县级
                        foreach (DataRow drx in drrr)
                        {
                            string TownDictMemo;
                            if (drx["DICTMEMO"].ToString() == drs["DICTMEMO"].ToString())
                            {
                                TownDictMemo = drx["DICTMEMO"].ToString();
                            }
                            else
                            {
                                TownDictMemo = drx["DICTMEMO"].ToString().Replace(drs["DICTMEMO"].ToString(), "");
                                TownDictMemo = TownDictMemo.Replace(dr["DICTMEMO"].ToString(), "");

                            }

                            strRtData += "{ \"id\": \"" + drx["TOWN"] + "\", \"name\": \"" + TownDictMemo + "\"},";
                        }
                        strRtData = strRtData.Substring(0, strRtData.Length - 1);
                        strRtData += "]},";
                    }
                    strRtData = strRtData.Substring(0, strRtData.Length - 1);
                    strRtData += "]},";
                }
                strRtData = strRtData.Substring(0, strRtData.Length - 1);
                strRtData += "]";
            }

            #endregion

            //clsRWFile myLog = new clsRWFile(); //将待执行的SQL语句记录下来
            //myLog.WriteInfoToTxtFile("SysLog", "wqw.txt", strRtData);

            return strRtData; //数据库查询结果
        }



    }
}
